package nc.bs.ia.hg.report;

import nc.bs.dao.BaseDAO;
import nc.jdbc.framework.util.SQLHelper;
import nc.vo.hg.ia.report.IaInvInOutReportVO;
import nc.vo.hg.ia.report.InOutSumPubVO;
import nc.vo.scm.pu.PuPubVO;

public class EstimateSumBO extends AbstractInOutSumQryBO {

	public EstimateSumBO(BaseDAO dao) {
		super(dao);
	}

	@Override
	public String buildSql(String date1, String date2, String whereCondition) {
		StringBuffer strb1 = new StringBuffer();
		
		strb1.append(" select h.pk_corp pk_corp, h.ccalbodyid crdcenterid");
		
		if(InOutSumPubVO.isinvcl.booleanValue()){
			strb1.append(",substr(invcode,0,2) cinvclid");
		}else{
			strb1.append(",inv.pk_invbasdoc cinvbasid,h.cinventoryid cinventoryid");
		}
		strb1.append(" ,sum(nvl(h.innervmizgnaccountnum, 0)) nnum,sum(round(h.innervminzgyfprice * (nvl(h.innervmizgnaccountnum, 0)), 2)) nmny ");
	    strb1.append(" from ic_vmi_sum h" );
	    		//" left outer join to_settlelist_b on h.cvmihid = to_settlelist_b.cfirstid inner join to_settlelist ");
	//    strb1.append(" on to_settlelist_b.csettlelistid = to_settlelist.csettlelistid 
	    strb1.append(" inner join bd_invmandoc man on man.pk_invmandoc = h.cinventoryid ");
	    strb1.append(" inner join bd_invbasdoc inv on inv.pk_invbasdoc = man.pk_invbasdoc where nvl(h.dr, 0) = 0 ");
	    strb1.append(" and h.pk_corp = '"+SQLHelper.getCorpPk()+"' ");
	    strb1.append(" and h.innervmidgaugedate >='"+date1+"' and h.innervmidgaugedate<='"+date2+"' and h.innervmibgaugeflag = 'Y' ");
	//  strb1.append(" and (to_settlelist.dr = 0) and (to_settlelist_b.dr = 0) and (to_settlelist.bfiflag = 'Y') ");
		if(PuPubVO.getString_TrimZeroLenAsNull(whereCondition)!=null){
			strb1.append(" and "+whereCondition);
		}
		strb1.append(" group by h.pk_corp,h.ccalbodyid");
		if(InOutSumPubVO.isinvcl.booleanValue()){
			strb1.append(",substr(invcode,0,2) ");
		}else{
			strb1.append(",inv.pk_invbasdoc,h.cinventoryid");
		}
		
        StringBuffer strb = new StringBuffer();
		
		strb.append(" select h.pk_corp pk_corp, h.ccalbodyid crdcenterid");
		
		if(InOutSumPubVO.isinvcl.booleanValue()){
			strb.append(",substr(invcode,0,2) cinvclid");
		}else{
			strb.append(",inv.pk_invbasdoc cinvbasid,h.cinventoryid cinventoryid");
		}
		strb.append("  ,sum(-nvl(to_settlelist_b.nnumber, 0)) nnum, sum(round(h.innervminzgyfprice * (-nvl(to_settlelist_b.nnumber, 0)),2)) nmny");
	    strb.append(" from ic_vmi_sum h left outer join to_settlelist_b on h.cvmihid = to_settlelist_b.cfirstid inner join to_settlelist ");
	    strb.append(" on to_settlelist_b.csettlelistid = to_settlelist.csettlelistid inner join bd_invmandoc man on man.pk_invmandoc = h.cinventoryid ");
	    strb.append(" inner join bd_invbasdoc inv on inv.pk_invbasdoc = man.pk_invbasdoc where nvl(h.dr, 0) = 0 ");
	    strb.append(" and h.pk_corp = '"+SQLHelper.getCorpPk()+"' ");
	    strb.append(" and to_settlelist.dbilldate >='"+date1+"' and to_settlelist.dbilldate<='"+date2+"' and h.innervmibgaugeflag = 'Y' ");
	    strb.append(" and (to_settlelist.dr = 0) and (to_settlelist_b.dr = 0) and (to_settlelist.bfiflag = 'Y') ");
	    strb.append(" and h.innervmidgaugedate >='"+date1+"' and h.innervmidgaugedate<='"+date2+"'");
		if(PuPubVO.getString_TrimZeroLenAsNull(whereCondition)!=null){
			strb.append(" and "+whereCondition);
		}
		strb.append(" group by h.pk_corp,h.ccalbodyid");
		if(InOutSumPubVO.isinvcl.booleanValue()){
			strb.append(",substr(invcode,0,2) ");
		}else{
			strb.append(",inv.pk_invbasdoc,h.cinventoryid");
		}
		String  s =null;
		if(InOutSumPubVO.isinvcl.booleanValue()){
			s = "select pk_corp,crdcenterid,cinvclid,sum (coalesce(nnum,0.0)) nnum,sum(coalesce(nmny,0.0)) nmny from (("+strb.toString()+") union all ("+strb1.toString()+")) group by pk_corp,crdcenterid,cinvclid ";
		}else{
			s = "select pk_corp,crdcenterid,cinvbasid,cinventoryid,sum (coalesce(nnum,0.0)) nnum,sum(coalesce(nmny,0.0)) nmny from (("+strb.toString()+") union all ("+strb1.toString()+")) group by pk_corp,crdcenterid,cinvbasid,cinventoryid ";
		}
		return s;
	}


	@Override
	protected void appData2(IaInvInOutReportVO data, InOutSumPubVO num) {
		if(data == null || num == null)
			return;
		data.setNestimatenum(num.getNnum());
		data.setNestimatemny(num.getNmny());
	
	}
}
